package com.hngm.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.hngm.bean.Company;
import com.hngm.bean.Product;
import com.hngm.bean.Producttypetable;
import com.hngm.util.DBManager;
import com.hngm.util.QueryDataResult;
import com.hngm.util.StringUtil;

import sun.security.action.GetLongAction;

public class ProducttypetableDao {
	//查询产品类型的方法
	public ArrayList<Producttypetable> queryProducttypetableList() {
		ArrayList<Producttypetable> prList = new ArrayList<Producttypetable>();
		// 定义sql语句
		String sql = "select * from producttypetable";
		// 调用DBManager查询数据库
		QueryDataResult qdr = DBManager.executeQuery(sql);
		// 从qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();
		try {
			// 遍历结果集rs
			while (rs.next()) {
				long id = rs.getLong("id");
				String productname = rs.getString("productname");
				long pid = rs.getLong("pid");
				int sort = rs.getInt("sort");
				String del = rs.getString("del");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				Producttypetable p = new Producttypetable(id, productname, pid, sort, del, creationtime, updatetime);
				prList.add(p);
			}
		} catch (SQLException e) {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return prList;
	}
	/**
	 * 根据满足条件得产品类型列表
	 */
	public ArrayList<Producttypetable> queryProductListBypType(String producttypename, String del, int beginIndex, int limit) {
		ArrayList<Producttypetable> pttList = new ArrayList<Producttypetable>();
		// 定义sql语句
		StringBuffer sb = new StringBuffer("select * from producttypetable where 1=1");

		// 拼接查询条件
		if (StringUtil.isNotNullorEmpty(producttypename)) {
			sb.append(String.format(" and productname like '%s'", "%" + producttypename + "%"));
		}
		if (StringUtil.isNotNullorEmpty(del)) {
			sb.append(String.format(" and del = '%s'", del));
		}
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sb.toString());
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String productname = rs.getString("productname");
				long pid = rs.getLong("pid");
				int sort = rs.getInt("sort");
				String del1 = rs.getString("del");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				Producttypetable p = new Producttypetable(id, productname, pid, sort, del, creationtime, updatetime);
				pttList.add(p);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return pttList;

	}

	public int queryProductListBypTypeCount(String producttypename, String del) {
		// 定义sql语句
				StringBuffer sb = new StringBuffer("select count(1) as count from producttypetable where 1=1");

				// 拼接查询条件
				if (StringUtil.isNotNullorEmpty(producttypename)) {
					sb.append(String.format(" and productname like '%s'", "%" + producttypename + "%"));
				}
				if (StringUtil.isNotNullorEmpty(del)) {
					sb.append(String.format(" and del = '%s'", del));
				}
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sb.toString());
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				return rs.getInt("count");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return 0;
	}
	public int insertProduttype(Producttypetable p) {
		String sql = String.format("insert into producttypetable(productname) values ('%s')", p.getProductname());
		return DBManager.executeUpdate(sql);
	}
	public int delProducttype(String productrypeId) {
		//定义删除产品的sql语句
		String sql = "delete from producttypetable where id = " + productrypeId;
		return DBManager.executeUpdate(sql);
	}
	public int editProduttype(Producttypetable p) {
		String sql = String.format("update producttypetable set productname='%s' where id = %d", p.getProductname(),p.getId());
		return DBManager.executeUpdate(sql);
		
	}
}
